package com.aier.user.service.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Component;

import com.aier.common.common.Constant;
import com.aier.common.dao.ICommonDAO;
import com.aier.user.entity.Dynamic;
import com.aier.user.entity.Letter;
import com.aier.user.entity.mapper.DynamicRowMapper;
import com.aier.user.entity.mapper.LetterDetailRowMapper;
import com.aier.user.entity.mapper.LetterRowMapper;
import com.aier.user.entity.mapper.NoticeRowMapper;
import com.aier.user.service.IUserMessageService;

@Component("userMessageService")
public class UserMessageServiceImpl_J implements IUserMessageService {

    @Resource(name = "commonDao")
    private ICommonDAO commonDao;

    public void setCommonDao(ICommonDAO commonDao) {
        this.commonDao = commonDao;
    }
    
    @Override
    public int deleteComment(Long fcid) {
        return commonDao.update("delete from fresh_comment where fcid = ?",new Object[]{fcid});
    }

    @Override
    public int insertLetter(Long suid, Long ruid, Short type, String content,
            Short state, String img) {
        
        Letter letter = (Letter) commonDao.listByObject("select * from letter where ((suid = ? and ruid=?) or (suid = ? and ruid = ?)) and type=1",
                new Object[]{suid,ruid,ruid,suid},new LetterRowMapper());
        if (null != letter) {
            // 两人之间已存在对话
            commonDao.update("update letter set suid=?,ruid=?,content=?,state=0,sdel=0,rdel=0,img=?,cdate=? where lid=?",
                    new Object[]{suid,ruid,content,img,new Timestamp(new Date().getTime()),letter.getLid()});
            commonDao.update("insert into letter_detail(suid,ruid,content,state,sdel,rdel,img,cdate,lid) values (?,?,?,?,?,?,?,?,?)",
                    new Object[]{suid,ruid,content,state,0,0,img,new Timestamp(new Date().getTime()),letter.getLid()});
            return Integer.parseInt(letter.getLid()+"");
        } else {
            // 两人直接不存在对话
            Letter let = new Letter();
            let.setCdate(new Timestamp(new Date().getTime()));
            let.setContent(content);
            let.setImg(img);
            let.setRdel((short)0);
            let.setRuid(ruid);
            let.setSdel((short)0);
            let.setState((short)0);
            let.setSuid(suid);
            let.setType((short)1);
            KeyHolder kh = commonDao.insert(LetterRowMapper.getPreparedStatementCreator(let),new GeneratedKeyHolder());
            commonDao.update("insert into letter_detail(suid,ruid,content,state,sdel,rdel,img,cdate,lid) values (?,?,?,?,?,?,?,?,?)",
                    new Object[]{suid,ruid,content,state,0,0,img,new Timestamp(new Date().getTime()),kh.getKey().longValue()});
            return kh.getKey().intValue();
        }
    }

    @Override
    public int deleteLetter(Long uid) {
        commonDao.update("update letter set sdel = 1 where suid = ? and type = 1",new Object[]{uid});
        commonDao.update("update letter set rdel = 1 where ruid = ?",new Object[]{uid});
        commonDao.update("update letter_detail set sdel = 1 where suid=?",new Object[]{uid});
        commonDao.update("update letter_detail set rdel = 1 where ruid=?",new Object[]{uid});
        return 1;
    }

    @Override
    public Map<String, Object> searchFollowLetter(int page,int pagesize,Long uid) {
        
        return commonDao.queryPage("select l.*, u.nickname, u.headimg from letter l left join user u on u.uid = l.suid " +
        		"where l.ruid = ? and l.rdel = 0 union select l.*, u.nickname, u.headimg from letter l left join user u " +
        		"on u.uid = l.ruid where (l.suid = ? and l.sdel = 0) and l.type = 1 order by cdate desc",
                page,pagesize,new Object[]{uid,uid},new LetterRowMapper());
    }

    @Override
    public Map<String, Object> searchLetterDetail(int page, int pagesize,
            Long lid) {
        return commonDao.queryPage("select ld.*,u.nickname,u.headimg from letter_detail ld left join letter l " +
        		"on l.lid = ld.lid left join `user` u on u.uid = ld.suid where ld.lid = ? and (( ld.suid = l.suid and ld.ruid = l.ruid " +
        		"and ld.sdel = 0 ) or ( ld.ruid = l.suid and ld.suid = l.ruid and ld.rdel = 0 )) order by ld.cdate desc",
        		page,pagesize,new Object[]{lid},new LetterDetailRowMapper());
    }

    @Override
    public int deleteLetterByLdid(Long uid, String ldidStr) {
        for (String ldid:ldidStr.split(",")) {
            commonDao.update("update letter_detail set sdel = 1 where ldid=? and suid = ?",new Object[]{ldid,uid});
            commonDao.update("update letter_detail set rdel = 1 where ldid=? and ruid = ?",new Object[]{ldid,uid});
        }
        if (commonDao.list("SELECT * from letter_detail  where lid = (select lid from letter_detail where ldid = ?) and ((suid = ? and sdel = 0) " +
        		"or (ruid = ? and rdel = 0))",new Object[]{ldidStr.split(",")[0],uid,uid},new LetterDetailRowMapper()).size()==0) {
            commonDao.update("update letter set sdel = 1 where suid = ? and lid = (select lid from letter_detail where ldid = ?)",
                    new Object[]{uid,ldidStr.split(",")[0]});
            commonDao.update("update letter set rdel = 1 where ruid = ? and lid = (select lid from letter_detail where ldid = ?)",
                    new Object[]{uid,ldidStr.split(",")[0]});
        }
        
        return 1;
    }

    @Override
    public int insertNotice(Long ruid, String content) {
        return commonDao.update("insert into notice(ruid,content,cdate) values (?,?,?)",
                new Object[]{ruid,content,new Timestamp(new Date().getTime())});
    }

    @Override
    public Map<String, Object> searchNoticeList(int page,int pagesize,Long uid) {
        return commonDao.queryPage("select * from notice where ruid=? order by cdate desc", page, pagesize, new Object[]{uid}, new NoticeRowMapper());
    }
    
    @Override
    public int deleteNotice(Long nid) {
        return commonDao.update("delete from notice where nid = ?",new Object[]{nid});
    }
    
    @Override
    public int deleteAllNotice(Long uid) {
        return commonDao.update("delete from notice where ruid = ?",new Object[]{uid});
    }

    @Override
    public String searchSpecialNew(Long uid) {
        
        return (String)commonDao.listByObject("select concat(u.nickname,':',f.content) as content,s.cdate from fresh f left join special s " +
        		"on s.relativeid = f.fid left join `user` u on u.uid = f.uid where s.suid = ? and s.type = 1 union " +
        		"select concat(u.nickname,':',fc.content) as content,s.cdate from fresh_comment fc left join special s " +
        		"on s.relativeid = fc.fcid left join `user` u on u.uid = fc.fcuid where s.suid = ? and s.type != 1 " +
        		"order by cdate desc limit 1", 
            	new Object[]{uid,uid}, new RowMapper<String>() {
                    @Override
                    public String mapRow(ResultSet rs, int arg1)
                        throws SQLException {
                        return rs.getString("content");
                    }
                }
        );
    }

    @Override
    public String searchCommentNew(Long uid) {
        
        return (String)commonDao.listByObject("select concat(u.nickname, ':', fc.content) as content from fresh_comment fc left join fresh f " +
        		"on f.fid = fc.fid left join user u on u.uid = fc.fcuid where f.uid = ? and fc.fcuid != ? order by fc.cdate desc limit 1", 
                new Object[]{uid,uid}, new RowMapper<String>() {
                    @Override
                    public String mapRow(ResultSet rs, int arg1)
                        throws SQLException {
                        return rs.getString("content");
                    }
                }
        );
        
    }
    
    @SuppressWarnings("unchecked")
	@Override
    public List<Dynamic> searchGetDynamicComment(Long uid) {
        return (List<Dynamic>)commonDao.list("select d.did,u.nickname,u.headimg, '评论了我的微博' as content, dc.cdate from dynamic_comment dc left join dynamic d " +
        		"on d.did = dc.did left join user u on u.uid = dc.uid where d.uid = ? and dc.uid != ? order by dc.cdate desc", 
                new Object[]{uid,uid}, new DynamicRowMapper());
        
    }
    
    @SuppressWarnings("unchecked")
	@Override
    public List<Dynamic> searchGetDynamicPraiseNew(Long uid) {
        
        return (List<Dynamic>)commonDao.list("select d.did,u.nickname,u.headimg, '赞了我的微博' as content, dp.cdate from dynamic_praise dp left join user u " +
        		"on u.uid = dp.uid left join dynamic d on d.did = dp.did where dp.uid != ? and d.uid = ? order by dp.cdate ", 
                new Object[]{uid,uid}, new DynamicRowMapper());
        
    }
    
    
    

    @Override
    public String searchPraiseNew(Long uid) {
        return (String)commonDao.listByObject("select concat(u.nickname, '赞了我的微博:', f.content) as content from fresh_praise fp left join user u " +
        		"on u.uid = fp.uid left join fresh f on f.fid = fp.fid where fp.uid != ? and f.uid = ? order by fp.cdate limit 1", 
                new Object[]{uid,uid}, new RowMapper<String>() {
                    @Override
                    public String mapRow(ResultSet rs, int arg1)
                        throws SQLException {
                        return rs.getString("content");
                    }
                }
        );
    }
    
    

    @Override
    public String searchLetterNew(Long uid) {
        return (String)commonDao.listByObject("select concat(u.nickname, ':', l.content) as content, l.cdate from letter l left join user u " +
        		"on u.uid = l.suid where l.ruid = ? and l.rdel = 0 union select concat(u.nickname, ':', l.content), " +
        		"l.cdate as content from letter l left join user u on u.uid = l.ruid where (l.suid = ? and l.sdel = 0) and l.type = 1 " +
        		"order by cdate desc limit 1", 
                new Object[]{uid,uid}, new RowMapper<String>() {
                    @Override
                    public String mapRow(ResultSet rs, int arg1)
                        throws SQLException {
                        return rs.getString("content");
                    }
                }
        );
    }

    @Override
    public String searchNoticeNew(Long uid) {
        return (String)commonDao.listByObject("select content from notice where ruid=? order by cdate desc limit 1", 
                new Object[]{uid}, new RowMapper<String>() {
                    @Override
                    public String mapRow(ResultSet rs, int arg1)
                        throws SQLException {
                        return rs.getString("content");
                    }
                }
        );
    }

    @Override
    public int insertAction(Long uid, Short type) {
        
        if (commonDao.list("select * from latest_action where uid = ? and type = ?",new Object[]{uid,type}).size() > 0) {
            return commonDao.update("update latest_action set cdate = ? where uid =? and type = ?",
                    new Object[]{new Timestamp(new Date().getTime()),uid,type});
        } else {
            return commonDao.update("insert into latest_action(uid,type,cdate) values(?,?,?)",
                    new Object[]{uid,type,new Timestamp(new Date().getTime())});
        }
    }

    @Override
    public List<String> insertOrSearchLatestNotice(Long uid) {
        List<String> strList = new ArrayList<String>();
        // 查询是否有新粉丝
        String fcount = (String) commonDao.listByObject("select count(*) as count from latest_action where uid = ? and type = 1", 
                new Object[]{uid},new RowMapper<String>() {
                    @Override
                    public String mapRow(ResultSet rs, int arg1)
                        throws SQLException {
                        return rs.getString("count");
                    }
                }
        );
        String cfans = "0";
        if ("0".equals(fcount)) {
            commonDao.update("insert into latest_action(uid,type,cdate) values(?,?,?)",new Object[]{uid,1,new Timestamp(new Date().getTime())});
        } else {
            cfans = (String)commonDao.listByObject(
                    "select count(*) as cfans from follow f left join latest_action l on f.fuid = l.uid where f.fuid = ? and l.type = 1 " +
                    "and f.cdate > l.cdate", 
                    new Object[]{uid},new RowMapper<String>() {
                        @Override
                        public String mapRow(ResultSet rs, int arg1)
                            throws SQLException {
                            return rs.getString("cfans");
                        }
                    }
            );
        }
        strList.add(cfans);
        
        // 查询是否有新@我的新鲜事
        String xcount = (String) commonDao.listByObject("select count(*) as count from latest_action where uid = ? and type = 2", 
                new Object[]{uid},new RowMapper<String>() {
                    @Override
                    public String mapRow(ResultSet rs, int arg1)
                        throws SQLException {
                        return rs.getString("count");
                    }
                }
        );
        String cfresh = "0";
        if ("0".equals(xcount)) {
            commonDao.update("insert into latest_action(uid,type,cdate) values(?,?,?)",new Object[]{uid,2,new Timestamp(new Date().getTime())});
        } else {
            cfresh = (String)commonDao.listByObject(
                    "select count(*) as cfresh from special s left join latest_action l on s.suid = l.uid where s.suid = ? and l.type = 2 " +
                    "and s.cdate > l.cdate", 
                    new Object[]{uid},new RowMapper<String>() {
                        @Override
                        public String mapRow(ResultSet rs, int arg1)
                            throws SQLException {
                            return rs.getString("cfresh");
                        }
                    }
            );
        }
        strList.add(cfresh);
        
        // 查询是否有新通知
        String ncount = (String) commonDao.listByObject("select count(*) as count from latest_action where uid = ? and type = 3", 
                new Object[]{uid},new RowMapper<String>() {
                    @Override
                    public String mapRow(ResultSet rs, int arg1)
                        throws SQLException {
                        return rs.getString("count");
                    }
                }
        );
        String cnotice = "0";
        if ("0".equals(ncount)) {
            commonDao.update("insert into latest_action(uid,type,cdate) values(?,?,?)",
                    new Object[]{uid,Constant.num_3,new Timestamp(new Date().getTime())});
        } else {
            cnotice = (String)commonDao.listByObject(
                    "select count(*) as cnotice from notice n left join latest_action l on n.ruid = l.uid where n.ruid = ? and l.type = 3 " +
                    "and n.cdate > l.cdate", 
                    new Object[]{uid},new RowMapper<String>() {
                        @Override
                        public String mapRow(ResultSet rs, int arg1)
                            throws SQLException {
                            return rs.getString("cnotice");
                        }
                    }
            );
        }
        strList.add(cnotice);
        
        // 查询是否有新私信
        String lcount = (String) commonDao.listByObject("select count(*) as count from latest_action where uid = ? and type = 4", 
                new Object[]{uid},new RowMapper<String>() {
                    @Override
                    public String mapRow(ResultSet rs, int arg1)
                        throws SQLException {
                        return rs.getString("count");
                    }
                }
        );
        String cletter = "0";
        if ("0".equals(lcount)) {
            commonDao.update("insert into latest_action(uid,type,cdate) values(?,?,?)",
                    new Object[]{uid,Constant.num_4,new Timestamp(new Date().getTime())});
        } else {
            cletter = (String)commonDao.listByObject(
                    "select count(*) as cletter from letter l left join latest_action la on l.ruid = la.uid where l.ruid = ? and la.type = 4 " +
                    "and l.cdate > la.cdate", 
                    new Object[]{uid},new RowMapper<String>() {
                        @Override
                        public String mapRow(ResultSet rs, int arg1)
                            throws SQLException {
                            return rs.getString("cletter");
                        }
                    }
            );
        }
        strList.add(cletter);
        
        return strList;
    }

}
